#coding:utf-8
import pandas as pd
import csv
import numpy as np
from datetime import datetime
#读取文件

dou1=pd.read_excel(r'G:\工作\每日\抖音1.xlsx',dtype={'子订单编号':str,'商品ID':str,'达人昵称':str,'达人ID':str,'选购商品':str})
dou2=pd.read_excel(r'G:\工作\每日\抖音2.xlsx',dtype={'子订单编号':str,'商品ID':str,'达人昵称':str,'达人ID':str,'选购商品':str})
dou3=pd.read_excel(r'G:\工作\每日\抖音3.xlsx',dtype={'子订单编号':str,'商品ID':str,'达人昵称':str,'达人ID':str,'选购商品':str})
dou4=pd.read_excel(r'G:\工作\每日\抖音4.xlsx',dtype={'子订单编号':str,'商品ID':str,'达人昵称':str,'达人ID':str,'选购商品':str})

ld=pd.read_excel(r'G:\工作\每日\刘栋达人.xlsx',dtype={'抖音号名称':str})

lxx1=pd.read_excel(r'G:\工作\每日\内部投流达人.xlsx',dtype={'抖音号名称':str}) #(桶装)（袋装）
px1=pd.read_excel(r'G:\工作\每日\飘雪.xlsx',dtype={'抖音号名称':str}) 
#lxx2=pd.read_excel(r'G:\工作\每日\罗欣欣土豆粉达人.xlsx',dtype={'抖音号名称':str}) #(土豆粉)
#xhwh1=pd.read_excel(r'G:\工作\每日\夕湖文化.xlsx',dtype={'抖音号名称':str}) 
#hn1=pd.read_excel(r'G:\工作\每日\花妞.xlsx',dtype={'抖音号名称':str}) 
z1=pd.read_excel(r'G:\工作\每日\招商1.xlsx',dtype={'订单id':str})
z2=pd.read_excel(r'G:\工作\每日\招商2.xlsx',dtype={'订单id':str})
z3=pd.read_excel(r'G:\工作\每日\招商3.xlsx',dtype={'订单id':str})
z4=pd.read_excel(r'G:\工作\每日\招商4.xlsx',dtype={'订单id':str})

y1=pd.read_excel(r'G:\工作\每日\佣金1.xlsx',dtype={'订单id':str})
y2=pd.read_excel(r'G:\工作\每日\佣金2.xlsx',dtype={'订单id':str})
y3=pd.read_excel(r'G:\工作\每日\佣金3.xlsx',dtype={'订单id':str})
y4=pd.read_excel(r'G:\工作\每日\佣金4.xlsx',dtype={'订单id':str})

#提取对应列
dou1_date=dou1.loc[:,['子订单编号','商品ID','选购商品','商家编码','商品数量','订单应付金额','订单状态','售后状态','流量来源','达人昵称','达人ID','广告渠道','流量类型','流量渠道','流量体裁','订单提交时间','平台实际承担优惠金额','达人实际承担优惠金额']]
dou1_date['平台']='抖音1'

dou2_date=dou2.loc[:,['子订单编号','商品ID','选购商品','商家编码','商品数量','订单应付金额','订单状态','售后状态','流量来源','达人昵称','达人ID','广告渠道','流量类型','流量渠道','流量体裁','订单提交时间','平台实际承担优惠金额','达人实际承担优惠金额']]
dou2_date['平台']='抖音2'

dou3_date=dou3.loc[:,['子订单编号','商品ID','选购商品','商家编码','商品数量','订单应付金额','订单状态','售后状态','流量来源','达人昵称','达人ID','广告渠道','流量类型','流量渠道','流量体裁','订单提交时间','平台实际承担优惠金额','达人实际承担优惠金额']]
dou3_date['平台']='抖音3'

dou4_date=dou4.loc[:,['子订单编号','商品ID','选购商品','商家编码','商品数量','订单应付金额','订单状态','售后状态','流量来源','达人昵称','达人ID','广告渠道','流量类型','流量渠道','流量体裁','订单提交时间','平台实际承担优惠金额','达人实际承担优惠金额']]
dou4_date['平台']='抖音4'

dou_date=pd.concat([dou1_date,dou2_date,dou3_date,dou4_date])
dou_date.columns=['订单id','商品ID','选购商品','商家编码','商品数量','订单应付金额','订单状态','售后状态','流量来源','达人昵称','达人ID','广告渠道','流量类型','流量渠道','流量体裁','订单提交时间','平台实际承担优惠金额','达人实际承担优惠金额','平台']
dou_date['订单提交时间']=pd.to_datetime(dou_date['订单提交时间'])
dou_date['日期']=dou_date['订单提交时间'].map(lambda x : datetime.date(x))
date_concat_s=dou_date[(dou_date['订单应付金额'] >= 0.01)]
date_concat_ss=date_concat_s[(date_concat_s['商家编码'] !='抖音直播间赠品')]
#['商家编码']去空格
date_concat_ss['商品ID']=date_concat_ss['商品ID'].str.strip()
date_concat_ss['达人ID']=date_concat_ss['达人ID'].str.strip()
date_concat_ss['商家编码']=date_concat_ss['商家编码'].str.strip()
date_concat_ss['订单id']=date_concat_ss['订单id'].str.strip()

date_concat_ss['成交金额']=np.where( (( date_concat_ss['订单状态']=="已签收")  | ( date_concat_ss['订单状态']=="待发货" ) | ( date_concat_ss['订单状态']=="已完成")  | ( date_concat_ss['订单状态']=="已发货")  | ( date_concat_ss['订单状态']=="已支付" ) | ( date_concat_ss['订单状态']=="部分发货" ))
                           &( date_concat_ss['售后状态'] != "退款成功") & (date_concat_ss['售后状态'] != "售后完成")  & (date_concat_ss['售后状态'] != "退款完成")  & (date_concat_ss['售后状态'] != "已全额退款" ),
                           date_concat_ss['订单应付金额']+date_concat_ss['平台实际承担优惠金额']+date_concat_ss['达人实际承担优惠金额'],0)

date_concat_ss['成交金额_不含优惠券']=np.where( (( date_concat_ss['订单状态']=="已签收")  | ( date_concat_ss['订单状态']=="待发货" ) | ( date_concat_ss['订单状态']=="已完成")  | ( date_concat_ss['订单状态']=="已发货")  | ( date_concat_ss['订单状态']=="已支付" ) | ( date_concat_ss['订单状态']=="部分发货" ))
                           &( date_concat_ss['售后状态'] != "退款成功") & (date_concat_ss['售后状态'] != "售后完成")  & (date_concat_ss['售后状态'] != "退款完成")  & (date_concat_ss['售后状态'] != "已全额退款" ),
                           date_concat_ss['订单应付金额'],0)

date_concat_ss['成交订单']=np.where( (( date_concat_ss['订单状态']=="已签收")  | ( date_concat_ss['订单状态']=="待发货" ) | ( date_concat_ss['订单状态']=="已完成")  | ( date_concat_ss['订单状态']=="已发货")  | ( date_concat_ss['订单状态']=="已支付" ) | ( date_concat_ss['订单状态']=="部分发货" ))
                           &( date_concat_ss['售后状态'] != "退款成功") & (date_concat_ss['售后状态'] != "售后完成")  & (date_concat_ss['售后状态'] != "退款完成")  & (date_concat_ss['售后状态'] != "已全额退款" ), 1,0)

date_concat_ss['退款金额']=np.where( (date_concat_ss['售后状态'] == '退款成功')|(date_concat_ss['售后状态'] =='退款完成')|(date_concat_ss['售后状态'] =='已全额退款')|(date_concat_ss['售后状态'] =='售后完成')|(date_concat_ss['售后状态'] =='退货退款完成')|(date_concat_ss['售后状态'] =='交易关闭'),
                           date_concat_ss['订单应付金额']+date_concat_ss['平台实际承担优惠金额']+date_concat_ss['达人实际承担优惠金额'],0)

date_concat_ss['退款金额_不含优惠券']=np.where( (date_concat_ss['售后状态'] == '退款成功')|(date_concat_ss['售后状态'] =='退款完成')|(date_concat_ss['售后状态'] =='已全额退款')|(date_concat_ss['售后状态'] =='售后完成')|(date_concat_ss['售后状态'] =='退货退款完成')|(date_concat_ss['售后状态'] =='交易关闭'),
                           date_concat_ss['订单应付金额'],0)
#佣金表
yj=pd.concat([y1,y2,y3,y4])
yj_date=yj.loc[:,['订单id','作者账号']] 
yj_date['订单id']=yj_date['订单id'].str.strip()
date_concat_sss2=pd.merge(date_concat_ss,yj_date,on='订单id',how='left')
date_concat_sss2['达人昵称'].fillna(date_concat_sss2['作者账号'],inplace=True)

#招商表
z1_date=z1.loc[:,['订单id','出单机构']]   
z2_date=z2.loc[:,['订单id','出单机构']]
z3_date=z3.loc[:,['订单id','出单机构']]
z4_date=z4.loc[:,['订单id','出单机构']]
zs=pd.concat([z1_date,z2_date,z3_date,z4_date])

date_concat_sss=pd.merge(date_concat_sss2,zs,on='订单id',how='left')

#各投流链接,达人

# lc_lj=['3752066905167495351','3754479940293230816']   #良辰链接
# lc_dr=[]   #良辰达人

ld_lj=['3524421208374397998','3718871706278953300','3764710268731523333']   #刘栋单独链接

ld_dr=list(ld['抖音号名称'])

lxx1_lj=['3702367630972289253','3753012046921335096','3701090938974765405','3506957255406950391','3520740088860524483',
         '3745946018349449594','3758543932670214558','3758958156538052831','3775109632327024899','3748189374425203566',
         '3751738608588030256','3752292420973363354','3737638082678227388','3628547567660489014','3694732441748111734',
         '3754689465759694891','3674502993446830541','3766406205405266422','3687141961262825560','3781986063870984612']#(罗欣欣达人）
lxx1_dr=list(lxx1['抖音号名称'])  #罗欣欣达人

px1_lj=['3751738608588030256']#(土豆粉达人)
px1_dr=list(px1['抖音号名称']) #罗欣欣土豆粉达人 

# xhwh1_lj=['3751738608588030256','3687141961262825560','3758958156538052831','3723151364247912548'] #夕湖文化
# xhwh1_dr=list(xhwh1['抖音号名称']) 

# hn1_lj=['3751738608588030256','3701090938974765405','3758958156538052831','3687141961262825560','3775109632327024899'] #花妞千川——爆品侠
# hn1_dr=list(hn1['抖音号名称']) 

def f(x,y,q,z,v,s,w,p):  #x: 链接 #y: 达人昵称  #q:达人ID #z:选购商品 #v:广告渠道 #s:流量来源 #w:出单机构  #p平台 
    if  w in['武汉海大电子商务有限公司'] :   
        return '嗨大'
    
    elif  w in['卓敏科技','紫金优选','瀚睿科技','红利传媒','来吉团队','金莎传媒'] :  
        return '冷漠'
    
    
    elif (x in ld_lj ) and ( (y in ld_dr )  or (pd.isnull(y))):  #去掉招商达人，加上自然单
        return '刘栋单独链接'
    elif(x in ['3751738608588030256']) and (y in ld_dr ) :
        return '刘栋'
    
    elif  y in['harry5'] : 
        return 'harry5'

    elif  q in['69526924387'] : 
        return '喜喜投流大号'

    elif  y in['饱饱吃不饱'] :   #罗欣欣运营账号
        return '罗欣欣账号'
    
    elif  y in['啦啦啦lalala','怪咖美食记','爱吃的光远','空壳少年','食光里的馋猫🐱','宛禾食品甄选号','大馋丫头可可','胖小宛'] : #白鹏飞运营账号
        return '白鹏飞账号'
    
    # elif (x in xhwh1_lj ) and (y in xhwh1_dr ) : 
    #     return '夕湖文化'

    elif (x in lxx1_lj ) and (y in lxx1_dr ) : 
        return '罗欣欣'
    
    elif (x in px1_lj ) and (y in px1_dr ) : 
         return '飘雪'
    # elif (x in hn1_lj ) and (y in hn1_dr ) : 
    #     return '花妞'
    # elif (x in lc_lj ) :
    #     return '莨辰'
    
    elif  x  in ['3735010733180715297','3712805099375034797']  : #罗欣欣单独链接
        return '罗欣欣账号'
    elif  x  in ['3723120728170692876','3723119143235485902','3741926701987398242','3550782895822129982','3526108849595662124','3699396698997653933']  : #白鹏飞单独链接
        return '白鹏飞账号'
      
    elif  x  in ['3714480675638870403','3741923461510070306']  : 
        return '共用链接自然单'
    
    #elif (v =='商品卡')and (x  in ['3712813994243915957','3735010733180715297','3713734480830267578','3712805099375034797','3712785316638228820','3738929837008617904','3746121270362767689','3741926278975062262','3714568370264342992','3743556622891483314','3719404361806381548','3741928376991088750','3738931359733907468','3668404150531653930','3686235918408221166','3699396698997653933','3686084716408209458']):
        #return '罗欣欣3店商品卡'
    
    #elif  (x  in ['3712813994243915957','3735010733180715297','3713734480830267578','3712805099375034797','3712785316638228820','3738929837008617904','3746121270362767689','3741926278975062262','3714568370264342992','3743556622891483314','3719404361806381548','3741928376991088750','3738931359733907468','3668404150531653930','3686235918408221166','3699396698997653933','3686084716408209458']):
        #return '罗欣欣账号'

    # elif (v =='商品卡')and (('lcc' in z)  or ('lxx' in z)):
    #     return '罗欣欣3店商品卡'
    
    # elif (('lcc' in z)  or ('lxx' in z)):
    #     return '共用链接自然单'
    
    elif y=='宛禾米线官方旗舰店' or ('z1' in z)  or (' Z1' in z):
        return '自播1'
    elif y=='宛禾米线速食官方旗舰店'  or y=='宛禾米线' or y=='宛禾速食官方旗舰店' or y=='宛禾速食直播间' or ('z3' in z)  or (' Z3' in z):
        return '自播3'
    elif y=='宛禾米线速食旗舰店' or ('lsf' in z) :
        return '螺蛳土豆粉直播间'
    elif y=='宛禾食品旗舰店' or ('z5' in z)  or ('Z5' in z) :
        return '自播5'
    elif y=='宛禾速食旗舰店' or y=='宛禾食品旗舰店甄选号' or ('z6' in z)  or ('Z6' in z):
        return '自播6'
    elif y=='宛禾官方旗舰店' or ('z8' in z)  or (' Z8' in z) :
        return '自播8'
    elif p=='抖音1' and ('dsp' in z) :
        return '自播1'
    elif p=='抖音2' and ('dsp' in z) :
        return '自播5'
    elif p=='抖音3' and ('dsp' in z) :
        return '自播8'
    elif p=='抖音4' and ('dsp' in z) :
        return '螺蛳土豆粉直播间'
    
    elif (x in ['3506957255406950391','3701090938974765405']) and (v =='商品卡'): #投流商品卡
        return '超级商品卡'
    
    elif pd.notnull(y):
        return '达人分发'
    elif s=='精选联盟':
        return '达人分发'
     
    else:
        return '自然单'
    

date_concat_sss['投流账户'] =list(map(f,list(date_concat_sss['商品ID']),list(date_concat_sss['达人昵称']),list(date_concat_sss['达人ID']),list(date_concat_sss['选购商品']),list(date_concat_sss['广告渠道']),list(date_concat_sss['流量来源']),list(date_concat_sss['出单机构']),list(date_concat_sss['平台'])))

def m(x,y,q,z,v,s,w):  #x: 链接 #y: 达人昵称 #z:选购商品 #v:广告渠道 #s:流量来源 #w:出单机构  #p平台 #q:达人ID
    if  w in['武汉海大电子商务有限公司'] :
        return '外部团队达人分发'
    
    elif  w in['卓敏科技','紫金优选','瀚睿科技','红利传媒','来吉团队','格森联盟','金莎传媒'] : 
        return '外部团队达人分发'
    
    
    
    elif (x in ld_lj ) and ( (y in ld_dr )  or (pd.isnull(y))): 
        return '内部短视频投流'
    elif(x in ['3751738608588030256']) and (y in ld_dr ) :
        return '内部短视频投流'
    
    elif  y in['harry5'] : 
        return '内部达人分发'
    
    elif  q in['69526924387'] : 
        return '内部达人分发'
    
    elif  y in['饱饱吃不饱'] :   #罗欣欣运营账号
        return '内部短视频投流'
    
    elif  y in['啦啦啦lalala','怪咖美食记','爱吃的光远','空壳少年','食光里的馋猫🐱','宛禾食品甄选号','大馋丫头可可','胖小宛'] : #白鹏飞运营账号
        return '内部短视频投流'
    
    # elif (x in xhwh1_lj ) and (y in xhwh1_dr ) : 
    #     return '外部短视频投流'

    elif (x in lxx1_lj ) and (y in lxx1_dr ) : 
        return '内部达人分发'
    
    elif (x in px1_lj ) and (y in px1_dr ) : 
         return '外部短视频投流'
    # elif (x in hn1_lj ) and (y in hn1_dr ) : 
    #     return '外部短视频投流'
    
    # elif (x in lc_lj ) :
    #     return '外部短视频投流'
    
    elif  x  in ['3735010733180715297','3712805099375034797']  : #罗欣欣单独链接
        return '内部短视频投流'
    elif  x  in ['3723120728170692876','3723119143235485902','3741926701987398242','3550782895822129982','3526108849595662124','3699396698997653933']  : #白鹏飞单独链接
        return '内部短视频投流'
    
    elif  x  in ['3714480675638870403','3741923461510070306']  : 
        return '内部短视频投流'
    
    
    #elif (v =='商品卡')and (x  in ['3712813994243915957','3735010733180715297','3713734480830267578','3712805099375034797','3712785316638228820','3738929837008617904','3746121270362767689','3741926278975062262','3714568370264342992','3743556622891483314','3719404361806381548','3741928376991088750','3738931359733907468','3668404150531653930','3686235918408221166','3699396698997653933','3686084716408209458']):
        #return '超级商品卡'
   
    # elif (v =='商品卡')and (('lcc' in z)  or ('lxx' in z)):
    #     return '超级商品卡'
    
    
    # elif  ('lcc' in z)  or ('lxx' in z):
    #     return '内部短视频投流'
    
    elif y=='宛禾米线官方旗舰店' or ('z1' in z)  or ('Z1' in z):
        return '自播'
    elif y=='宛禾米线速食官方旗舰店'  or y=='宛禾米线' or y=='宛禾速食官方旗舰店' or y=='宛禾速食直播间' or y=='宛禾速食' or ('z3' in z)  or ('Z3' in z):
        return '自播'
    elif y=='宛禾米线速食旗舰店' or ('lsf' in z) :
        return '自播'
    elif y=='宛禾食品旗舰店' or ('z5' in z)  or ('Z5' in z)or ('dsp' in z):
        return '自播'
    elif y=='宛禾速食旗舰店' or y=='宛禾食品旗舰店甄选号' or ('z6' in z)  or ('Z6' in z):
        return '自播'
    elif y=='宛禾官方旗舰店' or ('z8' in z)  or ('Z8' in z) :
        return '自播'
    
    elif  (x in ['3506957255406950391','3701090938974765405']) and  (v =='商品卡'): #投流商品卡
        return '超级商品卡'
    
    
    elif pd.notnull(y):
        return '内部达人分发'
    elif s=='精选联盟':
        return '内部达人分发'
    else:
        return '自然单'
    
date_concat_sss['总渠道'] =list(map(m,list(date_concat_sss['商品ID']),list(date_concat_sss['达人昵称']),list(date_concat_sss['达人ID']),list(date_concat_sss['选购商品']),list(date_concat_sss['广告渠道']),list(date_concat_sss['流量来源']),list(date_concat_sss['出单机构'])))    

result=date_concat_sss.groupby([date_concat_sss['日期'],date_concat_sss['平台'],date_concat_sss['总渠道'],date_concat_sss['投流账户']],as_index=False).agg(销售额_含优惠去退款=('成交金额','sum'),退款金额=('退款金额','sum'))

result.to_excel(r'G:\结果\投流销售额周度.xlsx')

result_bhyh=date_concat_sss.groupby([date_concat_sss['日期'],date_concat_sss['平台'],date_concat_sss['总渠道'],date_concat_sss['投流账户']],as_index=False).agg(销售额_不含优惠去退款=('成交金额_不含优惠券','sum'),成交单量=('成交订单','sum'),退款金额=('退款金额_不含优惠券','sum'))

result_bhyh.to_excel(r'G:\结果\投流销售额周度_不含优惠.xlsx')

date_SKU=date_concat_sss
result_sku=date_SKU.groupby([date_concat_sss['日期'],date_SKU['商品ID'],date_SKU['总渠道'],date_SKU['投流账户']],as_index=False).agg(销售额_含优惠去退款=('成交金额','sum'),退款金额=('退款金额','sum'))

result_sku.to_excel(r'G:\结果\投流销售额SKU周度.xlsx')


result_bm=date_SKU.groupby([date_concat_sss['日期'],date_SKU['商家编码'],date_SKU['总渠道'],date_SKU['投流账户']],as_index=False).agg(销售额_含优惠去退款=('成交金额','sum'),退款金额=('退款金额','sum'))

result_bm.to_excel(r'G:\结果\投流销售额编码周度.xlsx')